package common;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedHashMap;
import java.util.Map;

import javax.servlet.ServletException;

public class selectMenu {

	public Map<Integer,String> genreShow() throws ServletException {

		// 書籍ジャンル取得SQL文
		String sql = "select * from genres order by id";
		return getData(sql);
	}

	public Map<Integer,String> publisherShow() throws ServletException {

		// 書籍出版者取得SQL文
		String sql = "select * from publishers order by name";
		return getData(sql);

	}

	public Map<Integer,String> getData(String sql) throws ServletException {

		Connection con = null;
		Statement smt = null;

		try {
			con = DBManager.getConnection();
			smt = con.createStatement();
			ResultSet rs = smt.executeQuery(sql);

			Map<Integer,String> map = new LinkedHashMap<Integer,String>();

			while(rs.next()) {
				map.put(rs.getInt("ID"), rs.getString("NAME"));
			}

			return map;

		} catch(SQLException e) {
			throw new ServletException(e);
		} finally {
			if(smt != null) {
				try {
					smt.close();
				} catch(SQLException ignore) {
					// none
				}
			}
			if(con != null) {
				try {
					con.close();
				} catch(SQLException ignore) {
					// none
				}
			}
		}

	}

}
